﻿using System;
using System.Data;
using Dm;
using System.Collections.Generic;
using System.Data.Common;


namespace QQZiFramework.DataBase.DM
{
    public class DMAccess : IDBAccess
    {
        string lastSql;
        DmConnection conn;
        DmTransaction trans;
        //MySqlCommand cmd;
        IDataReader reader;
        string conStr;
        
        public DMAccess(DBContext context):base(context)
        {
            
        }

        public override string ConcatSql(params string[] args) 
        {
            return "concat("+ string.Join(',',args) +")";
        }

        /// <summary>
        /// 是否有自增功能
        /// </summary>
        /// <value></value>
        public override bool AotoIdentity
        {
            get { return true; }
        }
        
        public override string GetNewIdentitySql(string tableName)
        {
            return null;
        }

        static Dictionary<string, string> functionFormat = new Dictionary<string, string>();

        static DMAccess()
        {
            functionFormat.Add("date", "date_format({0}, '%Y-%m-%d')");
            functionFormat.Add("datetime", "date_format({0}, '%Y-%m-%d %H:%i:%S')");
            functionFormat.Add("isnull","IFNULL({0},{1})");
            functionFormat.Add("to_date","to_date({0},'%Y-%m-%d')");
            functionFormat.Add("to_datetime","to_date({0},'%Y-%m-%d %T')");
        }

        public override Dictionary<string, string> FunctionFormatMap
        {
            get { return functionFormat; }
        }

        private void OpenSql()
        {
            if (conn == null) conn = new DmConnection(conStr);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
        }

        public override void BeginTransaction()
        {
            if (trans != null)
            {
                throw new Exception("有未提交的事务，无法再创建！");
            }
            OpenSql();
            trans =  conn.BeginTransaction( IsolationLevel.ReadCommitted,false);
            mContext.LogSqlMessage("DataBase BeginTransaction", true);
        }

        public override void BulkInsert(string tableName, DataTable dataTable)
        {
            OpenSql();
            using (var bulkCopy = new DmBulkCopy(conn, DmBulkCopyOptions.Default, trans))
            {
                foreach (DataColumn dcPrepped in dataTable.Columns)
                {
                    bulkCopy.ColumnMappings.Add(dcPrepped.ColumnName, dcPrepped.ColumnName);
                }

                bulkCopy.BulkCopyTimeout = 660;
                bulkCopy.DestinationTableName = tableName;
                bulkCopy.WriteToServer(dataTable);
            }
        }

        public override void Close()
        {
            if (conn != null && conn.State == ConnectionState.Open)
            {
                if (reader != null && !reader.IsClosed) reader.Close();
                if (trans != null) { trans.Commit(); trans = null; }
                conn.Close();
            }
        }

        public override void Commit()
        {
            if (conn != null && conn.State == ConnectionState.Open)
            {
                if (trans != null)
                {
                    trans.Commit(); trans = null;
                    mContext.LogSqlMessage("DataBase Commit", true);
                }
            }
        }

        /// <summary>
        /// 获取属性的类型转为数据库中定义的类型
        /// </summary>
        public override string ConvertDataType(Type propertyType, bool withSize)
        {

            switch (propertyType.Name)
            {
                case "Int32":
                case "Int64":
                    return "int"; ;
                case "Single":
                case "Double":
                    return "float";
                case "Decimal":
                    if (withSize) return "decimal(18,2)";
                    else return "decimal";
                case "DateTime":
                    return "datetime";
                default:
                    if (withSize) return "varchar(255)";
                    else return "varchar";
            }
        }

        public override void Create(string connectionString)
        {
            conStr = connectionString;
        }
        
        public override IDbDataParameter CreateParameter(string name, object value)
        {
            return new DmParameter( ":"+name, value);
        }

        public override string CreateTempTable(string tableName, DataColumn[] allColumns, DataColumn createPrimaryKey)
        {
            if (allColumns.Length == 0) throw new ArgumentNullException("allColumns");
            string tmpName = "#" + tableName + DateTime.Now.ToString("dd");
            string sql = "CREATE TABLE " + tmpName + " (";
            string columnSql = "";
            foreach (var dc in allColumns)
            {
                columnSql += "," + dc.ColumnName + " " + ConvertDataType(dc.DataType, true);
                if (dc == createPrimaryKey) columnSql += " primary key";
            }
            sql += columnSql.Substring(1) + ")";
            ExecuteCommand(sql);
            return tmpName;
        }

        public override void CreateTreeExtendTableIfNotExists(string treeTableName)
        {
            ExecuteCommand(@"IF NOT EXISTS (SELECT 1 FROM Information_schema.tables WHERE Table_name = '" + treeTableName + @"_REX' )
                            BEGIN
                                Create TABLE " + treeTableName + @"_REX( NodeId int  not null,Deep int not null,SubNodeId int not Null)
                            END;");
        }

        /// <summary>
        /// 删除临时表
        /// </summary>
        public override void DropTempTable(string tableName)
        {
            ExecuteCommand("drop table #" + tableName);
        }

        public override object ExecScalar(string text, CommandType cmdType, params System.Data.IDbDataParameter[] parameters)
        {
            var cmd = CreateCommand(text, cmdType, parameters, text.IndexOf("insert", StringComparison.OrdinalIgnoreCase) == -1);
            object obj = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            cmd = null;
            return obj;
        }

        public override object ExecScalar(string sql)
        {
            return ExecScalar(sql, CommandType.Text);
        }

        public override int ExecuteCommand(string text, CommandType cmdType, params IDbDataParameter[] parameters)
        {
            var cmd = CreateCommand(text, cmdType, parameters, false);
            cmd.CommandType = cmdType;
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            cmd = null;
            return val;
        }

        public override int ExecuteCommand(string sql)
        {
            return ExecuteCommand(sql, CommandType.Text);
        }

        private DmCommand CreateCommand(string sql, CommandType cmdType, System.Data.IDbDataParameter[] parameters, bool query)
        {
            OpenSql();
            DmCommand cmd = new DmCommand(sql, conn);
            if (conn.ConnectionTimeout > 60) cmd.CommandTimeout = conn.ConnectionTimeout;
            cmd.CommandText = sql;
            cmd.CommandType = cmdType;
            if (parameters != null && parameters.Length > 0)
            {
                cmd.Parameters.AddRange(parameters);
                sql += "(";
                foreach (DmParameter parameter in parameters)
                {
                    sql += parameter.ParameterName + "=";
                    if (parameter.Value != null && parameter.Value.ToString().Length > 255) sql += "长内容";
                    else sql += parameter.Value;
                    sql += ",";
                }
                sql = sql.Substring(0, sql.Length - 1) + ")";
            }
            if (trans != null) cmd.Transaction = trans;
            lastSql = sql;
            mContext.LogSqlMessage(sql, query);
            return cmd;
        }

        public override IDbConnection GetConnection()
        {
            return conn;
        }

        public override string GetDateConverter(int length)
        {
            return "{0}";
        }

        public override string GetInsertedIdentitySql(string tableName)
        {
            return "select LAST_INSERT_ID()";
        }

        public override string GetLastSql()
        {
            return lastSql;
        }

        public override ForeignKeyItem[] GetTableForeignKeys(string tableName)
        {
            throw new NotImplementedException();
        }

        public override string[] GetTablePrimaryKeys(string tableName)
        {
            var lst = new List<string>();
            string sql = string.Format("SELECT COLUMN_NAME FROM USER_CONSTRAINTS JOIN USER_CONS_COLUMNS ON USER_CONSTRAINTS.CONSTRAINT_NAME = USER_CONS_COLUMNS.CONSTRAINT_NAME WHERE USER_CONSTRAINTS.CONSTRAINT_TYPE = 'P' AND USER_CONSTRAINTS.TABLE_NAME = '{0}' AND USER_CONSTRAINTS.OWNER='{1}'", tableName,GetDBName());
            using (IDataReader reader = QueryReader(sql, CommandType.Text, null))
            {
                while (reader.Read())
                {
                    lst.Add(reader[0].ToString());
                }
                reader.Close();
            }
            return lst.ToArray();
        }

        public override Dictionary<string, string> GetTables()
        {
            var dt = QueryTable("select object_name NAME,object_name OBJ_COMMENT from all_objects where object_type='TABLE' AND TEMPORARY ='N' and OWNER='"+GetDBName()+"'");
            var result = new Dictionary<string, string>();
            foreach (DataRow row in dt.Rows)
            {
                string comment = null;
                if (row["OBJ_COMMENT"] != DBNull.Value) comment = row["OBJ_COMMENT"].ToString();
                result.Add((string)row["NAME"], comment);
            }
            return result;
        }

        public override bool IsColumnExists(string tableName, string columnName)
        {
            object scalar = ExecScalar("SELECT COLUMN_NAME FROM DBA_TAB_COLUMNS WHERE TABLE_NAME = '"+tableName+"' AND COLUMN_NAME = '"+columnName+"' AND OWNER='"+ GetDBName() +"'");
            if (scalar != null && scalar != DBNull.Value) return true;
            else return false;
        }

        public override bool IsTableExists(string tableName)
        {
            string sql = "select 1 from all_objects where OBJECT_NAME ='" + tableName + "' and object_type='TABLE' and OWNER ='"+GetDBName()+"'";
            object scalar = ExecScalar(sql);
            if (scalar != null && scalar != DBNull.Value) return true;
            else return false;
        }

        public override String PartQuerySql(string select, string table, string where, string orderby, int start, int end)
        {
            if (orderby == null) throw new ArgumentNullException("pagger query order by");
            start--;
            end--;
            string sql = "select " + select + " from " + table;
            if (!string.IsNullOrEmpty(where)) sql += " where " + where;
            sql += " order by " + orderby;
            sql += " limit " + start + "," + (end - start + 1);
            return sql;
        }

        // public override String PartQuerySql(string select, string table, string where, string orderby, IDbDataParameter pageIndex, IDbDataParameter pageSize){
        //      if (orderby == null) throw new ArgumentNullException("pagger query order by");
        //       string sql = "select " + select + " from " + table;
        //     if (!string.IsNullOrEmpty(where)) sql += " where " + where;
        //     sql += " order by " + orderby;
        //     sql += " limit " + pageIndex.ParameterName + "-1 * "+ pageSize.ParameterName +"," + pageSize.ParameterName;
        //     return sql;
        // } 

        public override IDataReader QueryReader(string sql, CommandType cmdType, params System.Data.IDbDataParameter[] parameters)
        {
            if (reader != null && !reader.IsClosed) throw new ClientException("有已打开的Reader，请先调用reader.close方法关闭");
            var cmd = CreateCommand(sql, cmdType, parameters, true);
            reader = cmd.ExecuteReader();
            cmd.Parameters.Clear();
            cmd = null;
            return reader;
        }

        public override DataTable QueryTable(string text, CommandType cmdType, params System.Data.IDbDataParameter[] parameters)
        {
            var cmd = CreateCommand(text, cmdType, parameters, true);
            if (parameters != null)
            {
                cmd.Parameters.Clear();
                cmd.Parameters.AddRange(parameters);
            }
            System.Data.DataTable dt = new DataTable();
            DmDataAdapter da = new DmDataAdapter(cmd);
            da.Fill(dt);
            cmd.Parameters.Clear();
            cmd = null;
            return dt;
        }

        public override DataTable QueryTable(string sql)
        {
            return QueryTable(sql, CommandType.Text);
        }

        public override void Rollback()
        {
            if (conn != null && conn.State == ConnectionState.Open)
            {
                if (trans != null)
                {
                    trans.Rollback(); trans = null;
                    mContext.LogSqlMessage("DataBase Rollback", true);
                }
            }
        }

        /// <summary>
        /// 根据临时表的内容，更新物理表
        /// <param name="primaryKey">主键名</param>
        /// <param name="updateColumns">要更新的列</param>
        /// </summary>
        public override void UpdateByTempTable(string tableName, string tempTableFullName, DataColumn[] updateColumns, DataColumn primaryKeyColumn)
        {
            var primaryKey = primaryKeyColumn.ColumnName;
            string sql = "update a set a." + updateColumns[0].ColumnName + "=b." + updateColumns[0].ColumnName;
            for (int i = 1; i < updateColumns.Length; i++)
            {
                sql += ",a." + updateColumns[i].ColumnName + "=b." + updateColumns[i].ColumnName;
            }
            sql += " from " + tableName + " a  join " + tempTableFullName + " b on a." + primaryKey + " = b." + primaryKey;
            ExecuteCommand(sql);
        }

        private string GetDBName()
        {
            string dbName = Utility.getMiddle(conStr, "DATABASE=", ";"); 
            if (dbName == null) dbName = Utility.getMiddle(conStr, "Catalog=", ";");
            if (dbName == null) throw new Exception("连接串中没有Catalog=xxx或者DATABASE");
            return dbName;
        }

        public override void Dispose()
        {
            if (conn != null)
            {
                if (conn.State == ConnectionState.Open)
                {
                    if (trans != null) trans.Rollback();
                    if (reader != null && !reader.IsClosed) reader.Close();
                    conn.Close();
                }
                //if (cmd != null) cmd.Dispose();
                conn.Dispose();
                conn = null;
                //cmd = null;
                trans = null;
            }
        }
    }
}
